package com.tokenmaster.aitrade.data;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.Queue;

import javax.naming.spi.DirStateFactory.Result;

import org.apache.log4j.Logger;
import org.omg.CosNaming.NamingContextExtPackage.StringNameHelper;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.tokenmaster.aitrade.common.system.KLinePoint;
import com.tokenmaster.aitrade.common.system.KLineType;
import com.tokenmaster.aitrade.common.system.MarketInfo;
import com.tokenmaster.aitrade.common.system.Order_Spot;
import com.tokenmaster.aitrade.common.system.SymbolInfo;
import com.tokenmaster.aitrade.common.ticker.TickerData;
import com.tokenmaster.aitrade.trade.boll.Index_Boll_Info;
import com.tokenmaster.aitrade.trade.kdj.Index_KDJ_Info;

/**
 * @Description: 指标分析系统
 * @author buxinyun
 * @date 2018年6月1日
 *
 */
public class SystemDB {

	private static SystemDB instance = new SystemDB();

	public static SystemDB getInstance() {
		return instance;
	}

	private Logger logger = Logger.getLogger(this.getClass());

	private ComboPooledDataSource cpds = new ComboPooledDataSource();

	private final String KLINE_POINT_TABLE = "klinepoint";

	private final String INDEX_BOLL_TABLE = "klinepoint_boll";

	private final String MARKET_SYMBOL_TABLE = "market_symbol";

	private final String MARKET_INFO_TABLE = "market_info";

	private final String UPANDDOWN_TABLE = "upanddown";

	private final String ORDERLIST_TABLE = "orderlist";

	private final String CONFIG_TALBE = "config";

	private final String ASSETLIST_TABLE = "assetlist";

	public SystemDB() {
		// 从配置文件中读取数据库连接信息
		Properties propfile = new Properties();
		try {
			InputStream inputStream = new BufferedInputStream(new FileInputStream("conf.properties"));
			propfile.load(inputStream);

			String name = propfile.getProperty("name");
			String url = propfile.getProperty("url");
			String user = propfile.getProperty("user");
			String pass = propfile.getProperty("pass");

			inputStream.close();

			logger.info("DBUrl = " + url);

			cpds.setDriverClass(name);
			cpds.setJdbcUrl(url);
			cpds.setUser(user);
			cpds.setPassword(pass);
			cpds.setMaxStatements(180);
			cpds.setMaxPoolSize(100);

		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 插入K线数据
	 * 
	 * @param type
	 * @param kLinePoint
	 */
	public void insertKLine(KLinePoint kLinePoint) {

		String sql = "insert into  " + KLINE_POINT_TABLE
				+ "  (open,high,low,last,vol,unixtime,type,symbol) values(?,?,?,?,?,?,?,?)";

		try {
			// 获取数据连接
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, kLinePoint.getOpen());
			pstmt.setDouble(2, kLinePoint.getHigh());
			pstmt.setDouble(3, kLinePoint.getLow());
			pstmt.setDouble(4, kLinePoint.getLast());
			pstmt.setDouble(5, kLinePoint.getVol());
			pstmt.setLong(6, kLinePoint.getUnixTime());
			pstmt.setString(7, kLinePoint.getType());
			pstmt.setString(8, kLinePoint.getSymbol());
			pstmt.executeUpdate();

			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 更新K线
	 * 
	 * @param kLinePoint
	 */
	public void updateKLine(KLinePoint kLinePoint) {

		String sql = "update " + KLINE_POINT_TABLE
				+ " set high = ?,low=?,last=? where unixtime = ? and type = ? and symbol=?";

		Connection conn;

		try {
			conn = cpds.getConnection();

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, kLinePoint.getHigh());
			pstmt.setDouble(2, kLinePoint.getLow());
			pstmt.setDouble(3, kLinePoint.getLast());
			pstmt.setLong(4, kLinePoint.getUnixTime());
			pstmt.setString(5, kLinePoint.getType());
			pstmt.setString(6, kLinePoint.getSymbol());

			pstmt.executeUpdate();

			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public KLinePoint getKLinePointBack(String symbol, String type, long unixtime) {

		KLinePoint kLinePoint = null;

		String sql = "select * from  " + KLINE_POINT_TABLE
				+ " where  symbol=? and type = ? and unixtime>? order by unixtime limit 1";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setString(2, type);
			pstmt.setLong(3, unixtime);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				kLinePoint = new KLinePoint();
				kLinePoint.setSymbol(rs.getString("symbol"));
				kLinePoint.setOpen(rs.getDouble("open"));
				kLinePoint.setHigh(rs.getDouble("high"));
				kLinePoint.setLow(rs.getDouble("low"));
				kLinePoint.setLast(rs.getDouble("last"));
				kLinePoint.setVol(rs.getDouble("vol"));
				kLinePoint.setUnixTime(rs.getLong("unixtime"));
				kLinePoint.setType(rs.getString("type"));

			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return kLinePoint;
	}

	public KLinePoint getKLinePoint(String symbol, String type, long unixtime) {

		KLinePoint kLinePoint = null;

		String sql = "select * from  " + KLINE_POINT_TABLE + " where  symbol=? and type = ? and unixtime=? ";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setString(2, type);
			pstmt.setLong(3, unixtime);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				kLinePoint = new KLinePoint();
				kLinePoint.setSymbol(rs.getString("symbol"));
				kLinePoint.setOpen(rs.getDouble("open"));
				kLinePoint.setHigh(rs.getDouble("high"));
				kLinePoint.setLow(rs.getDouble("low"));
				kLinePoint.setLast(rs.getDouble("last"));
				kLinePoint.setVol(rs.getDouble("vol"));
				kLinePoint.setUnixTime(rs.getLong("unixtime"));
				kLinePoint.setType(rs.getString("type"));

			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return kLinePoint;
	}

	public KLinePoint getLastKLinePoint(String symbol, String type) {

		KLinePoint kLinePoint = null;

		String sql = "select * from  " + KLINE_POINT_TABLE
				+ " where  symbol=? and type = ? order by unixtime desc limit 1";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setString(2, type);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				kLinePoint = new KLinePoint();
				kLinePoint.setSymbol(rs.getString("symbol"));
				kLinePoint.setOpen(rs.getDouble("open"));
				kLinePoint.setHigh(rs.getDouble("high"));
				kLinePoint.setLow(rs.getDouble("low"));
				kLinePoint.setLast(rs.getDouble("last"));
				kLinePoint.setVol(rs.getDouble("vol"));
				kLinePoint.setUnixTime(rs.getLong("unixtime"));
				kLinePoint.setType(rs.getString("type"));

			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return kLinePoint;
	}

	public List<KLinePoint> getKLinePoints(String symbol, String type, int limitNum) {

		List<KLinePoint> kLinePoints = new ArrayList<KLinePoint>();

		String sql = "select * from  " + KLINE_POINT_TABLE
				+ " where  symbol=? and type = ? order by unixtime desc limit ?";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setString(2, type);
			pstmt.setInt(3, limitNum);

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				KLinePoint kLinePoint = new KLinePoint();
				kLinePoint.setSymbol(rs.getString("symbol"));
				kLinePoint.setOpen(rs.getDouble("open"));
				kLinePoint.setHigh(rs.getDouble("high"));
				kLinePoint.setLow(rs.getDouble("low"));
				kLinePoint.setLast(rs.getDouble("last"));
				kLinePoint.setVol(rs.getDouble("vol"));
				kLinePoint.setUnixTime(rs.getLong("unixtime"));
				kLinePoint.setType(rs.getString("type"));

				kLinePoints.add(kLinePoint);
			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return kLinePoints;
	}

	public List<KLinePoint> getKLinePointsBefore(String symbol, String type, long unixTime, int limitNum) {

		String sql = "select * from  " + KLINE_POINT_TABLE
				+ " where  symbol=? and type = ? and unixtime<=? order by unixtime desc limit ?";

		List<KLinePoint> kLinePoints = new ArrayList<KLinePoint>();

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setString(2, type);
			pstmt.setLong(3, unixTime);
			pstmt.setInt(4, limitNum);

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				KLinePoint kLinePoint = new KLinePoint();
				kLinePoint.setSymbol(rs.getString("symbol"));
				kLinePoint.setOpen(rs.getDouble("open"));
				kLinePoint.setHigh(rs.getDouble("high"));
				kLinePoint.setLow(rs.getDouble("low"));
				kLinePoint.setLast(rs.getDouble("last"));
				kLinePoint.setVol(rs.getDouble("vol"));
				kLinePoint.setUnixTime(rs.getLong("unixtime"));
				kLinePoint.setType(rs.getString("type"));

				kLinePoints.add(0, kLinePoint);
			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return kLinePoints;
	}

	public long getKLinePoints(String symbol, String type, long unixTime, int limitNum, Queue<KLinePoint> kLinePoints) {

		long unixtime = -1;

		String sql = "select * from  " + KLINE_POINT_TABLE
				+ " where  symbol=? and type = ? and unixtime>? order by unixtime limit ?";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setString(2, type);
			pstmt.setLong(3, unixTime);
			pstmt.setInt(4, limitNum);

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				KLinePoint kLinePoint = new KLinePoint();
				kLinePoint.setSymbol(rs.getString("symbol"));
				kLinePoint.setOpen(rs.getDouble("open"));
				kLinePoint.setHigh(rs.getDouble("high"));
				kLinePoint.setLow(rs.getDouble("low"));
				kLinePoint.setLast(rs.getDouble("last"));
				kLinePoint.setVol(rs.getDouble("vol"));
				kLinePoint.setUnixTime(rs.getLong("unixtime"));
				kLinePoint.setType(rs.getString("type"));

				unixtime = rs.getLong("unixtime");

				kLinePoints.offer(kLinePoint);
			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return unixtime;
	}

	public void insertIndexBollInfo(String marketName, String symbol, String contract_type, String type,
			Index_Boll_Info index_Boll_Info) {

		String sql = "insert into  " + INDEX_BOLL_TABLE
				+ "  (market,unixtime,symbol,contract_type,type,upprice,downprice,double_upprice,double_downprice,midprice,currentprice,position)"
				+ " values(?,?,?,?,?,?,?,?,?,?,?,?)";

		try {
			// 获取数据连接
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, marketName);
			pstmt.setLong(2, index_Boll_Info.getUnixTime());
			pstmt.setString(3, symbol);
			pstmt.setString(4, contract_type);
			pstmt.setString(5, type);
			pstmt.setDouble(6, index_Boll_Info.getUpPrice());
			pstmt.setDouble(7, index_Boll_Info.getDownPrice());
			pstmt.setDouble(8, index_Boll_Info.getDouble_upPrice());
			pstmt.setDouble(9, index_Boll_Info.getDouble_downPrice());
			pstmt.setDouble(10, index_Boll_Info.getMidPrice());
			pstmt.setDouble(11, index_Boll_Info.getCurrentPrice());
			pstmt.setInt(12, index_Boll_Info.getPosition());
			pstmt.executeUpdate();

			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public void insertIndexBollInfo(String symbol, String type, Index_Boll_Info index_Boll_Info) {
		String sql = "insert into  " + INDEX_BOLL_TABLE
				+ "  (symbol,type,unixtime,upprice,downprice,double_upprice,double_downprice,midprice,currentprice,position)"
				+ " values(?,?,?,?,?,?,?,?,?,?)";

		try {
			// 获取数据连接
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setString(2, type);
			pstmt.setLong(3, index_Boll_Info.getUnixTime());
			pstmt.setDouble(4, index_Boll_Info.getUpPrice());
			pstmt.setDouble(5, index_Boll_Info.getDownPrice());
			pstmt.setDouble(6, index_Boll_Info.getDouble_upPrice());
			pstmt.setDouble(7, index_Boll_Info.getDouble_downPrice());
			pstmt.setDouble(8, index_Boll_Info.getMidPrice());
			pstmt.setDouble(9, index_Boll_Info.getCurrentPrice());
			pstmt.setInt(10, index_Boll_Info.getPosition());
			pstmt.executeUpdate();

			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public void updateIndexBollInfo(Index_Boll_Info index_Boll_Info) {

	}

	public Index_Boll_Info getIndexBollInfo(String symbol, String type, long unixtime) {
		Index_Boll_Info index_Boll_Info = null;

		String sql = "select * from  " + INDEX_BOLL_TABLE + " where  symbol=? and type = ? and unixtime=? ";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setString(2, type);
			pstmt.setLong(3, unixtime);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				index_Boll_Info = new Index_Boll_Info();
				index_Boll_Info.setSymbol(rs.getString("symbol"));
				index_Boll_Info.setUnixTime(rs.getLong("unixtime"));
				index_Boll_Info.setType(rs.getString("type"));
				index_Boll_Info.setUpPrice(rs.getDouble("upprice"));
				index_Boll_Info.setDownPrice(rs.getDouble("downprice"));
				index_Boll_Info.setDouble_upPrice(rs.getDouble("double_upprice"));
				index_Boll_Info.setDouble_downPrice(rs.getDouble("double_downprice"));
				index_Boll_Info.setMidPrice(rs.getDouble("midprice"));
				index_Boll_Info.setCurrentPrice(rs.getDouble("currentprice"));
				index_Boll_Info.setPosition(rs.getInt("position"));
			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return index_Boll_Info;

	}

	public Index_Boll_Info getLastIndexBollInfo(String symbol, String type) {
		Index_Boll_Info index_Boll_Info = null;

		String sql = "select * from  " + INDEX_BOLL_TABLE
				+ " where  symbol=? and type = ? order by unixtime desc limit 1";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setString(2, type);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				index_Boll_Info = new Index_Boll_Info();
				index_Boll_Info.setSymbol(rs.getString("symbol"));
				index_Boll_Info.setUnixTime(rs.getLong("unixtime"));
				index_Boll_Info.setType(rs.getString("type"));
				index_Boll_Info.setUpPrice(rs.getDouble("upprice"));
				index_Boll_Info.setDownPrice(rs.getDouble("downprice"));
				index_Boll_Info.setDouble_upPrice(rs.getDouble("double_upprice"));
				index_Boll_Info.setDouble_downPrice(rs.getDouble("double_downprice"));
				index_Boll_Info.setMidPrice(rs.getDouble("midprice"));
				index_Boll_Info.setCurrentPrice(rs.getDouble("currentprice"));
				index_Boll_Info.setPosition(rs.getInt("position"));
			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return index_Boll_Info;

	}

	public Index_Boll_Info getUnPushIndexBollInfo(String symbol) {

		Index_Boll_Info index_Boll_Info = null;

		String sql = "select * from  " + INDEX_BOLL_TABLE + " where  symbol=? and ispushed=0";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, symbol);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				index_Boll_Info = new Index_Boll_Info();
				index_Boll_Info.setSymbol(rs.getString("symbol"));
				index_Boll_Info.setUnixTime(rs.getLong("unixtime"));
				index_Boll_Info.setType(rs.getString("type"));
				index_Boll_Info.setUpPrice(rs.getDouble("upprice"));
				index_Boll_Info.setDownPrice(rs.getDouble("downprice"));
				index_Boll_Info.setDouble_upPrice(rs.getDouble("double_upprice"));
				index_Boll_Info.setDouble_downPrice(rs.getDouble("double_downprice"));
				index_Boll_Info.setMidPrice(rs.getDouble("midprice"));
				index_Boll_Info.setCurrentPrice(rs.getDouble("currentprice"));
				index_Boll_Info.setPosition(rs.getInt("position"));
			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return index_Boll_Info;
	}

	public List<SymbolInfo> getSymbols(String market) {
		List<SymbolInfo> symbolInfos = new ArrayList<SymbolInfo>();

		String sql = "select * from  " + MARKET_SYMBOL_TABLE + " where  market=?";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, market);

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				SymbolInfo symbolInfo = new SymbolInfo();
				symbolInfo.setMarket(market);
				symbolInfo.setName(rs.getString("symbol"));
				symbolInfo.setShowName(rs.getString("showname"));

				symbolInfos.add(symbolInfo);
			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return symbolInfos;
	}

	public List<KLineType> getKLineTyps() {
		List<KLineType> kLineTypes = new ArrayList<KLineType>();

		String sql = "select * from  " + MARKET_SYMBOL_TABLE;

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				KLineType kLineType = new KLineType();
				kLineType.setName(rs.getString("name"));
				kLineType.setShowName(rs.getString("showname"));

				kLineTypes.add(kLineType);
			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return kLineTypes;
	}

	public List<MarketInfo> getMarketInfo() {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * @param symbol
	 * @param last
	 * @param date
	 */
	public void insertIndexUpAndDown(String symbol, double last, long date, String type, double rang, long timeInterval,
			double buyPrice, double sellPrice) {

		String sql = "insert into  " + UPANDDOWN_TABLE
				+ "  (symbol,last,unixtime,type,rang,timeinterval,buyprice,sellprice) values(?,?,?,?,?,?,?,?)";

		try {
			// 获取数据连接
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setDouble(2, last);
			pstmt.setLong(3, date);
			pstmt.setString(4, type);
			pstmt.setDouble(5, rang);
			pstmt.setLong(6, timeInterval);
			pstmt.setDouble(7, buyPrice);
			pstmt.setDouble(8, sellPrice);
			pstmt.executeUpdate();

			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 获取之前的支撑点数据
	 *
	 * @param unixtime
	 * @return
	 */
	public List<TickerData> getTickerDatas(long beforeTime) {

		List<TickerData> tickerDatas = new ArrayList<TickerData>();

		String sql = "select * from " + UPANDDOWN_TABLE + "  where unixtime < ? order by unixtime desc";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setLong(1, beforeTime);

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				TickerData tickerData = new TickerData();
				tickerData.setLast(rs.getDouble("last"));
				tickerData.setUnixtime(rs.getLong("unixtime"));

				tickerDatas.add(tickerData);
			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return tickerDatas;
	}

	public Index_KDJ_Info getIndexKDJ(String symbol, String type, long unixtime) {

		String sql = "select * from  " + KLINE_POINT_TABLE
				+ " where symbol=? and type=? and unixtime = ? order by unixtime desc limit 1";

		Index_KDJ_Info index_KDJ_Info = null;

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setString(2, type);
			pstmt.setLong(3, unixtime);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {

				index_KDJ_Info = new Index_KDJ_Info();
				index_KDJ_Info.setSymbol(rs.getString("symbol"));
				index_KDJ_Info.setUnixtime(rs.getLong("unixtime"));
				index_KDJ_Info.setType(rs.getString("type"));
				index_KDJ_Info.setkValue(rs.getDouble("k"));
				index_KDJ_Info.setdValue(rs.getDouble("d"));
				index_KDJ_Info.setjValue(rs.getDouble("j"));

			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return index_KDJ_Info;
	}

	public Index_KDJ_Info getLastIndexKDJ(String symbol, String type, long unixtime) {

		String sql = "select * from  " + KLINE_POINT_TABLE
				+ " where symbol=? and type=? and unixtime < ? order by unixtime desc limit 1";

		Index_KDJ_Info index_KDJ_Info = null;

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setString(2, type);
			pstmt.setLong(3, unixtime);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {

				index_KDJ_Info = new Index_KDJ_Info();
				index_KDJ_Info.setSymbol(rs.getString("symbol"));
				index_KDJ_Info.setUnixtime(rs.getLong("unixtime"));
				index_KDJ_Info.setType(rs.getString("type"));
				index_KDJ_Info.setkValue(rs.getDouble("k"));
				index_KDJ_Info.setdValue(rs.getDouble("d"));
				index_KDJ_Info.setjValue(rs.getDouble("j"));

			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return index_KDJ_Info;
	}

	/**
	 * 更新KDJ数据
	 * 
	 * @param index_KDJ_Info
	 */
	public void updateIndexKDJInfo(Index_KDJ_Info index_KDJ_Info) {

		String sql = "update " + KLINE_POINT_TABLE
				+ " set k = ?,d = ?,j = ? where unixtime = ? and type = ? and symbol=?";

		Connection conn;

		try {
			conn = cpds.getConnection();

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, index_KDJ_Info.getkValue());
			pstmt.setDouble(2, index_KDJ_Info.getdValue());
			pstmt.setDouble(3, index_KDJ_Info.getjValue());
			pstmt.setLong(4, index_KDJ_Info.getUnixtime());
			pstmt.setString(5, index_KDJ_Info.getType());
			pstmt.setString(6, index_KDJ_Info.getSymbol());

			pstmt.executeUpdate();

			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void updateIndexKDJOperation(Index_KDJ_Info index_KDJ_Info, String operation) {

		String sql = "update " + KLINE_POINT_TABLE + " set operation = ? where unixtime = ? and type = ? and symbol=?";

		Connection conn;

		try {
			conn = cpds.getConnection();

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, operation);
			pstmt.setLong(2, index_KDJ_Info.getUnixtime());
			pstmt.setString(3, index_KDJ_Info.getType());
			pstmt.setString(4, index_KDJ_Info.getSymbol());

			pstmt.executeUpdate();

			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void insertRobotOrder(String robotName, Order_Spot order_Spot, double position, double avg_price,
			double totalposition) {

		String sql = "insert into  " + ORDERLIST_TABLE
				+ " (symbol,type,price,amount,create_date,robot,poisition,avg_price,totalposition) values(?,?,?,?,?,?,?,?,?)";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, order_Spot.getSymbol());
			pstmt.setString(2, order_Spot.getType());
			pstmt.setDouble(3, order_Spot.getPrice());
			pstmt.setDouble(4, order_Spot.getAmount());
			pstmt.setLong(5, order_Spot.getCreate_date());
			pstmt.setString(6, robotName);
			pstmt.setDouble(7, position);
			pstmt.setDouble(8, avg_price);
			pstmt.setDouble(9, totalposition);

			pstmt.executeUpdate();

			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public String getConfig(String key) {

		String result = "";

		String sql = "select * from " + CONFIG_TALBE + "  where keyname = ?";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, key);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				result = rs.getString("value");
			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}

	public String getConfig(String group, String key) {

		String result = "";

		String sql = "select * from " + CONFIG_TALBE + "  where groupname = ? and keyname = ?";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, group);
			pstmt.setString(2, key);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				result = rs.getString("value");
			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}

	public double getAvgPriceFromAccount(String accountName, String coinName) {

		double result = 0;

		String sql = "select * from " + ASSETLIST_TABLE + "  where accountname = ? and coinname = ?";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, accountName);
			pstmt.setString(2, coinName);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				result = rs.getDouble("avg_price");
			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;

	}

	public double getAccount(String accountName, String coinName) {

		double result = 0;

		String sql = "select * from " + ASSETLIST_TABLE + "  where accountname = ? and coinname = ?";

		try {
			Connection conn = cpds.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, accountName);
			pstmt.setString(2, coinName);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				result = rs.getDouble("coinnum");
			}
			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;

	}

	public void updateAccount(String accountName, String coinName, double coinNum, double avgprice) {

		String sql = "update " + ASSETLIST_TABLE
				+ " set coinnum = ?,avg_price=? where accountname = ? and coinname = ?";

		Connection conn;

		try {
			conn = cpds.getConnection();

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, coinNum);
			pstmt.setDouble(2, avgprice);
			pstmt.setString(3, accountName);
			pstmt.setString(4, coinName);

			pstmt.executeUpdate();

			pstmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
